﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using VacationsRequest.Models;
using PagedList;
using System.Collections;
using System.Web.Security;
using System.Net.Mail;
using System.Data.Objects.SqlClient;

namespace VacationsRequest.Controllers
{
    public class VacationDaysController : Controller
    {
        private VacationDayDBContext db = new VacationDayDBContext();
        private VacationApprovalDBContext dbApproval = new VacationApprovalDBContext();
        private EmployeeDBContext dbEmployee = new EmployeeDBContext();
        private ManagerDBContext dbManagers = new ManagerDBContext();
        private TeamDBContext dbTeams = new TeamDBContext();
        private PositionDBContext dbPositions = new PositionDBContext();
        private HolidaysDayDBContext dbHolidays = new HolidaysDayDBContext();

        //
        // GET: /VacationDays/
        [Authorize]
        public ViewResult Index()
        {
            return View(db.VacationDays.ToList());
        }

        //
        // GET: /VacationDays/Details/5
        [AcceptVerbs(HttpVerbs.Get)]
        public JsonResult HolidaysList()
        {


            var holidays = (from u in dbHolidays.Holidays
                            orderby u.Date ascending
                            select u.Date).ToList().Select(d => d.ToString("yyyyMd")).ToList();


            return Json(holidays.ToArray(), JsonRequestBehavior.AllowGet);

        }

        [Authorize]
        public ViewResult Details(int id, int? page, int? page2)
        {
            //  VacationDay vacationday = db.VacationDays.Find(id);

            if (Request.HttpMethod != "GET")
            {
                page = 1;
                page2 = 1;
            }

            Employee employee = dbEmployee.Employees.Find(id);

            ViewBag.ManagerName = (from u in dbManagers.Managers
                                   where u.ID == employee.IdManager
                                   select u.FirstName + " " + u.LastName).First();

            ViewBag.TeamName = (from u in dbTeams.Teams
                                where u.ID == employee.IdTeam
                                select u.Name).First();

            ViewBag.PositionName = (from u in dbPositions.Positions
                                    where u.ID == employee.IdPosition
                                    select u.Name).First();

            ViewBag.CurrentEmployee = employee;

            ViewBag.TotalVacations = db.Database.SqlQuery<int>("SELECT dbo.fnGetTotalDays(" + id + ", GetDate())").First();
            ViewBag.TakenDays = db.Database.SqlQuery<int>("SELECT dbo.fnTakenDays(" + id + ", GetDate())").First();
            ViewBag.RemainingDays = db.Database.SqlQuery<int>("SELECT dbo.fnRemainingDays(" + id + ", GetDate())").First();
            ViewBag.ReservedDays = db.Database.SqlQuery<int>("SELECT dbo.fnGetReservedDays(" + id + ", GetDate())").First();

            var vacationsTaken = (from u in db.VacationDays
                                  where u.IdEmployee == id & u.Date <= DateTime.Now
                                  select u);

            var vacationsReserved = (from u in db.VacationDays
                                     where u.IdEmployee == id & u.Date > DateTime.Now
                                     select u);

            vacationsTaken = vacationsTaken.OrderByDescending(s => s.Date);
            vacationsReserved = vacationsReserved.OrderByDescending(s => s.Date);

            int pageSize = 10;
            int pageNumber = (page ?? 1);
            int pageNumber2 = (page2 ?? 1);

            ViewBag.VacationsListTaken = vacationsTaken.ToPagedList(pageNumber, pageSize);
            ViewBag.VacationsLisReserved = vacationsReserved.ToPagedList(pageNumber2, pageSize);

            return View();
        }

        [Authorize]
        [HttpPost]
        public ActionResult Details(VacationTimeSpan vts, int id, int? page, int? page2)
        {

            Employee employee = dbEmployee.Employees.Find(id);

            ViewBag.ManagerName = (from u in dbManagers.Managers
                                   where u.ID == employee.IdManager
                                   select u.FirstName + " " + u.LastName).First();

            ViewBag.TeamName = (from u in dbTeams.Teams
                                where u.ID == employee.IdTeam
                                select u.Name).First();

            ViewBag.PositionName = (from u in dbPositions.Positions
                                    where u.ID == employee.IdPosition
                                    select u.Name).First();

            ViewBag.CurrentEmployee = employee;

            if (ModelState.IsValid)
            {
                TimeSpan tsp = vts.ToDate - vts.FromDate;
                DateTime currentDate;
                Guid approvalId = Guid.NewGuid();
                List<VacationDay> validDates = new List<VacationDay>();

                var holidaysList = (from u in dbHolidays.Holidays
                                    select u.Date).ToArray();

                for (int i = 0; i <= tsp.Days; i++)
                {
                    currentDate = vts.FromDate.AddDays(i);

                    if (currentDate.DayOfWeek != DayOfWeek.Saturday && currentDate.DayOfWeek != DayOfWeek.Sunday)
                    {
                        //System.Diagnostics.Debugger.Log(1, "App", vts.FromDate.AddDays(i).ToShortDateString());
                        //System.Diagnostics.Debugger.Log(1, "App", "\n");
                        if (!holidaysList.Contains(currentDate))
                        {
                            if (!db.VacationDays.Any(u => u.Date == currentDate && u.IdEmployee == id))
                            {
                                VacationDay vday = new VacationDay();

                                vday.Comment = vts.Comment;
                                vday.Date = currentDate;
                                vday.IdEmployee = id;
                                vday.IdApproval = approvalId;


                                validDates.Add(vday);
                            }
                        }
                    }
                }

                if (validDates.Count != 0)
                {
                    VacationApproval vacationapproval = new VacationApproval();


                    vacationapproval.ID = approvalId;
                    vacationapproval.DateCreated = DateTime.Now;
                    vacationapproval.IdEmployee = id;
                    vacationapproval.Comment = vts.Comment;
                    vacationapproval.Deleted = false;
                    vacationapproval.Notified = false;
                    vacationapproval.NumDays = validDates.Count;
                    vacationapproval.IdStatus = 1;
                    vacationapproval.IdUserApprove = employee.IdUserApprover;

                    vacationapproval.FromDate = validDates[0].Date;
                    vacationapproval.ToDate = validDates[validDates.Count - 1].Date;

                    dbApproval.VacationApproval.Add(vacationapproval);
                    dbApproval.SaveChanges();

                    for (int i = 0; i < validDates.Count; i++)
                    {
                        db.VacationDays.Add(validDates[i]);
                        db.SaveChanges();
                    }

                    SendEmail(employee, vacationapproval.FromDate, vacationapproval.ToDate, vacationapproval.ID);

                }

            }

            var vacationsTaken = (from u in db.VacationDays
                                  where u.IdEmployee == id & u.Date <= DateTime.Now
                                  select u);

            var vacationsReserved = (from u in db.VacationDays
                                     where u.IdEmployee == id & u.Date > DateTime.Now
                                     select u);

            vacationsTaken = vacationsTaken.OrderByDescending(s => s.Date);
            vacationsReserved = vacationsReserved.OrderByDescending(s => s.Date);

            int pageSize = 10;
            int pageNumber = (page ?? 1);
            int pageNumber2 = (page2 ?? 1);

            ViewBag.VacationsListTaken = vacationsTaken.ToPagedList(pageNumber, pageSize);
            ViewBag.VacationsLisReserved = vacationsReserved.ToPagedList(pageNumber2, pageSize);

            return RedirectToAction("Details");

        }

        [Authorize]
        public ActionResult EmployeeDetails(int id, int? page, int? page2)
        {
            //  VacationDay vacationday = db.VacationDays.Find(id);

            string userEmail = Membership.GetUser(User.Identity.Name, true /* userIsOnline */).Email;

            var temployeeId = (from u in dbEmployee.Employees
                               where u.Email.Trim().ToLower() == userEmail.Trim().ToLower()
                               select u.ID).First();

            if (temployeeId != id)
            {
                return RedirectToAction("EmployeeDetails", "VacationDays", new { id = temployeeId });
            }


            if (Request.HttpMethod != "GET")
            {
                page = 1;
                page2 = 1;
            }

            Employee employee = dbEmployee.Employees.Find(id);

            ViewBag.ManagerName = (from u in dbManagers.Managers
                                   where u.ID == employee.IdManager
                                   select u.FirstName + " " + u.LastName).First();

            ViewBag.TeamName = (from u in dbTeams.Teams
                                where u.ID == employee.IdTeam
                                select u.Name).First();

            ViewBag.PositionName = (from u in dbPositions.Positions
                                    where u.ID == employee.IdPosition
                                    select u.Name).First();

            var approvals = dbApproval.VacationApproval.Include(v => v.Status);

            approvals = approvals.Where(u => u.IdEmployee == id);
            approvals = approvals.Where(u => u.Deleted == false);

            ViewBag.VacationApprovals = approvals.ToList();


            ViewBag.CurrentEmployee = employee;

            ViewBag.TotalVacations = db.Database.SqlQuery<int>("SELECT dbo.fnGetTotalDays(" + id + ", GetDate())").First();
            ViewBag.TakenDays = db.Database.SqlQuery<int>("SELECT dbo.fnTakenDays(" + id + ", GetDate())").First();
            ViewBag.RemainingDays = db.Database.SqlQuery<int>("SELECT dbo.fnRemainingDays(" + id + ", GetDate())").First();
            ViewBag.ReservedDays = db.Database.SqlQuery<int>("SELECT dbo.fnGetReservedDays(" + id + ", GetDate())").First();

            var vacationsTaken = (from u in db.VacationDays
                                  where u.IdEmployee == id & u.Date <= DateTime.Now
                                  select u);

            var vacationsReserved = (from u in db.VacationDays
                                     where u.IdEmployee == id & u.Date > DateTime.Now
                                     select u);

            vacationsTaken = vacationsTaken.OrderByDescending(s => s.Date);
            vacationsReserved = vacationsReserved.OrderByDescending(s => s.Date);

            int pageSize = 10;
            int pageNumber = (page ?? 1);
            int pageNumber2 = (page2 ?? 1);

            ViewBag.VacationsListTaken = vacationsTaken.ToPagedList(pageNumber, pageSize);
            ViewBag.VacationsLisReserved = vacationsReserved.ToPagedList(pageNumber2, pageSize);

            return View();
        }

        [Authorize]
        [HttpPost]
        public ActionResult EmployeeDetails(VacationTimeSpan vts, int id, int? page, int? page2)
        {

            Employee employee = dbEmployee.Employees.Find(id);

            ViewBag.ManagerName = (from u in dbManagers.Managers
                                   where u.ID == employee.IdManager
                                   select u.FirstName + " " + u.LastName).First();

            ViewBag.TeamName = (from u in dbTeams.Teams
                                where u.ID == employee.IdTeam
                                select u.Name).First();

            ViewBag.PositionName = (from u in dbPositions.Positions
                                    where u.ID == employee.IdPosition
                                    select u.Name).First();

            var approvals = dbApproval.VacationApproval.Include(v => v.Status);

            approvals = approvals.Where(u => u.IdEmployee == id);
            approvals = approvals.Where(u => u.Deleted == false);

            ViewBag.VacationApprovals = approvals.ToList();

            ViewBag.CurrentEmployee = employee;

            if (ModelState.IsValid)
            {
                TimeSpan tsp = vts.ToDate - vts.FromDate;
                DateTime currentDate;
                Guid approvalId = Guid.NewGuid();
                List<VacationDay> validDates = new List<VacationDay>();

                var holidaysList = (from u in dbHolidays.Holidays
                                    select u.Date).ToArray();

                for (int i = 0; i <= tsp.Days; i++)
                {
                    currentDate = vts.FromDate.AddDays(i);

                    if (currentDate.DayOfWeek != DayOfWeek.Saturday && currentDate.DayOfWeek != DayOfWeek.Sunday)
                    {
                        //System.Diagnostics.Debugger.Log(1, "App", vts.FromDate.AddDays(i).ToShortDateString());
                        //System.Diagnostics.Debugger.Log(1, "App", "\n");
                        if (!holidaysList.Contains(currentDate))
                        {
                            if (!db.VacationDays.Any(u => u.Date == currentDate && u.IdEmployee == id))
                            {
                                VacationDay vday = new VacationDay();

                                vday.Comment = vts.Comment;
                                vday.Date = currentDate;
                                vday.IdEmployee = id;
                                vday.IdApproval = approvalId;

                                validDates.Add(vday);

                            }
                        }
                    }
                }

                if (validDates.Count != 0)
                {
                    VacationApproval vacationapproval = new VacationApproval();


                    vacationapproval.ID = approvalId;
                    vacationapproval.DateCreated = DateTime.Now;
                    vacationapproval.IdEmployee = id;
                    vacationapproval.Comment = vts.Comment;
                    vacationapproval.Deleted = false;
                    vacationapproval.Notified = false;
                    vacationapproval.NumDays = validDates.Count;
                    vacationapproval.IdStatus = 1;
                    vacationapproval.IdUserApprove = employee.IdUserApprover;

                    vacationapproval.FromDate = validDates[0].Date;
                    vacationapproval.ToDate = validDates[validDates.Count - 1].Date;

                    dbApproval.VacationApproval.Add(vacationapproval);
                    dbApproval.SaveChanges();

                    for (int i = 0; i < validDates.Count; i++)
                    {
                        db.VacationDays.Add(validDates[i]);
                        db.SaveChanges();
                    }

                    SendEmail(employee, vacationapproval.FromDate, vacationapproval.ToDate, vacationapproval.ID);

                }


            }

            var vacationsTaken = (from u in db.VacationDays
                                  where u.IdEmployee == id & u.Date <= DateTime.Now
                                  select u);

            var vacationsReserved = (from u in db.VacationDays
                                     where u.IdEmployee == id & u.Date > DateTime.Now
                                     select u);

            vacationsTaken = vacationsTaken.OrderByDescending(s => s.Date);
            vacationsReserved = vacationsReserved.OrderByDescending(s => s.Date);

            int pageSize = 10;
            int pageNumber = (page ?? 1);
            int pageNumber2 = (page2 ?? 1);

            ViewBag.VacationsListTaken = vacationsTaken.ToPagedList(pageNumber, pageSize);
            ViewBag.VacationsLisReserved = vacationsReserved.ToPagedList(pageNumber2, pageSize);

            return RedirectToAction("EmployeeDetails");

        }

        private void SendEmail(Employee emp, DateTime pstart, DateTime pend, Guid apprId)
        {
            System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient();

            string baseUrl = Request.Url.Scheme + System.Uri.SchemeDelimiter + Request.Url.Host + (Request.Url.IsDefaultPort ? "" : ":" + Request.Url.Port);

            MailMessage msg = new MailMessage();

            string apprEmail = Membership.GetUser(emp.IdUserApprover).Email;

            if (pstart > DateTime.Now)
            {
                msg.To.Add(emp.Email);
                msg.Body = "You have requested vacations from " + pstart.ToShortDateString() + " to " + pend.ToShortDateString() + ".";
                msg.Subject = "Vacation Request - Vacations Requested";
                msg.Priority = MailPriority.High;
                msg.IsBodyHtml = true;
                smtp.Send(msg);
            }
            else
            {

                msg.To.Add(apprEmail);
                msg.Body = "You have requested vacations from " + pstart.ToShortDateString() + " to " + pend.ToShortDateString() + " for the employee " + (emp.FirstName + " " + emp.FirstLastName + " " + emp.SecondLastName).Trim();
                msg.Subject = "Vacation Request - Vacations Requested";
                msg.Priority = MailPriority.High;
                msg.IsBodyHtml = true;
                smtp.Send(msg);
            }

            string lnk = baseUrl + "/ApprovalStatus/Edit/" + apprId.ToString();
            string returnLink = "<a href='" + lnk + "'>" + lnk + "</a>";

            msg = new MailMessage();
            msg.To.Add(apprEmail);
            msg.Body = "The employee " + (emp.FirstName + " " + emp.FirstLastName + " " + emp.SecondLastName).Trim() + " has requested vacations from " + pstart.ToShortDateString() + " to " + pend.ToShortDateString() + ".<br /> Request Link: " + returnLink;
            msg.Subject = "Vacation Request - Action Required - Vacations Requested";
            msg.Priority = MailPriority.High;
            msg.IsBodyHtml = true;
            smtp.Send(msg);
        }

        //
        // GET: /VacationDays/Create
        [Authorize]
        public ActionResult Create()
        {
            return View();
        }

        //
        // POST: /VacationDays/Create
        [Authorize]
        [HttpPost]
        public ActionResult Create(VacationDay vacationday)
        {
            if (ModelState.IsValid)
            {
                db.VacationDays.Add(vacationday);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(vacationday);
        }

        //
        // GET: /VacationDays/Edit/5
        [Authorize]
        public ActionResult Edit(int id)
        {
            VacationDay vacationday = db.VacationDays.Find(id);
            return View(vacationday);
        }

        //
        // POST: /VacationDays/Edit/5
        [Authorize]
        [HttpPost]
        public ActionResult Edit(VacationDay vacationday)
        {
            if (ModelState.IsValid)
            {
                db.Entry(vacationday).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(vacationday);
        }

        //
        // GET: /VacationDays/Delete/5
        [Authorize]
        public ActionResult Delete(int id)
        {
            VacationDay vacationday = db.VacationDays.Find(id);
            int idEmp = vacationday.IdEmployee;
            db.VacationDays.Remove(vacationday);
            db.SaveChanges();
            return RedirectToAction("Details", new { id = idEmp });
        }

        [Authorize]
        [HttpGet]
        public PartialViewResult VacationsListPartial(int id, string yearString, string type)
        {
            Employee employee = dbEmployee.Employees.Find(id);
            ViewBag.CurrentEmployee = employee;
            ViewBag.Type = type;

            var vacationdays = (from u in db.VacationDays
                                where u.IdEmployee == id
                                select u);

            var YearLst = new List<string>();
            var YearQry = (from u in db.VacationDays
                           where u.IdEmployee == id
                           select SqlFunctions.StringConvert((double)u.Date.Year).Trim()).Distinct();

            YearLst.AddRange(YearQry);

            ViewBag.yearString = new SelectList(YearLst);

            if (String.IsNullOrEmpty(type))
            {
                type = "";
            }

            if (type.Equals("Taken"))
            {
                vacationdays = vacationdays.Where(u => u.Date <= DateTime.Now);
                ViewBag.ReportType = "Taken Vacations by ";
            }
            else
            {
                vacationdays = vacationdays.Where(u => u.Date > DateTime.Now);
                ViewBag.ReportType = "Reserved Vacations by ";
            }

            

            var allVacations = vacationdays.ToArray();
            

            var allApprovals = (from u in dbApproval.VacationApproval
                                where u.IdEmployee == id
                                select u).ToArray();

            var result = from u in allVacations
                         join z in allApprovals on u.IdApproval equals z.ID
                        into outer
                         from o in outer.DefaultIfEmpty()
                      select new EmployeeVacationDetails
                      {
                          VacDate = u.Date,
                          Comment = u.Comment,
                          Status = ((o == null) ? "" : o.Status.Description),
                          ApprovalDate = ((o == null) ? null : o.ApprovalDate) 

                      };

            if (!String.IsNullOrEmpty(yearString))
            {
                int tmpYear = int.Parse(yearString);
                result = result.Where(x => x.VacDate.Value.Year == tmpYear);
            }

            result = result.OrderByDescending(s => s.VacDate);

            return PartialView(result);
        }

        [AcceptVerbs(HttpVerbs.Get)]
        public JsonResult VacationsListJson(int id, string type)
        {


            var vacationdays = (from u in db.VacationDays
                                orderby u.Date descending
                                where u.IdEmployee == id
                                select u);

            if (String.IsNullOrEmpty(type))
            {
                type = "";
            }

            if (type.Equals("Taken"))
            {
                vacationdays = vacationdays.Where(u => u.Date <= DateTime.Now);
            }
            else
            {
                vacationdays = vacationdays.Where(u => u.Date > DateTime.Now);
            }

            var vacArray = vacationdays.ToArray();

            var selectedDays = vacArray.Select(d => d.Date.ToString("yyyyMd")).ToList();

            return Json(selectedDays.ToArray(), JsonRequestBehavior.AllowGet);

        }

        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }
    }
}